package tienda.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import tienda.exception.DAOExcepcion;
import tienda.modelo.Persona;
import tienda.util.ConexionBD;


public class PersonaDAO extends BaseDAO {

	public Persona insertar(Persona vo) throws DAOExcepcion {
		String query = "insert into persona(nombres, paterno, materno, sexo, email, fecnacimiento, codUsuario, contrasena, " +
				"TipoPersona_idTipoPersona, celular, documento, nroDocumento) " +
				"values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, vo.getNombres());
			stmt.setString(2, vo.getPaterno());
			stmt.setString(3, vo.getMaterno());
			stmt.setString(4, vo.getSexo());
			stmt.setString(5, vo.getEmail());
			stmt.setString(6, vo.getFecnacimiento());
			stmt.setString(7, vo.getCodUsuario());
			stmt.setString(8, vo.getContrasena());
			stmt.setInt(9, vo.getTipoPersona_idTipoPersona());
			stmt.setString(10, vo.getCelular());
			stmt.setString(11, vo.getDocumento());
			stmt.setString(12, vo.getNroDocumento());
				
								
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			vo.setIdPersona(id);

		} catch (SQLException e) {
			//System.err.println(e.getSQLState());
			throw new DAOExcepcion(e.getSQLState());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public Persona obtener(int idPersona) throws DAOExcepcion {
		Persona vo = new Persona();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select nroDocumento, codUsuario, email,nombres,paterno,materno,celular from persona where idPersona=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, idPersona);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				vo.setNroDocumento(rs.getString(1));
				vo.setCodUsuario(rs.getString(2));
				vo.setEmail(rs.getString(3));
				vo.setNombres(rs.getString(4));
				vo.setPaterno(rs.getString(5));
				vo.setMaterno(rs.getString(6));
				vo.setCelular(rs.getString(7));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	
	 public Collection<Persona> buscarPorNombre(String nombre)
	            throws DAOExcepcion {
	        String query = "select idPersona, nroDocumento, email from persoma where nombre like ?";
	        Collection<Persona> lista = new ArrayList<Persona>();

	        Connection con = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        try {
	            con = ConexionBD.obtenerConexion();
	            stmt = con.prepareStatement(query);
	            stmt.setString(1, "%" + nombre + "%");

	            rs = stmt.executeQuery();
	            while (rs.next()) {
	                Persona vo = new Persona();
	                vo.setIdPersona(rs.getInt("idPersona"));
	                vo.setNroDocumento(rs.getString("nroDocumento"));
	                vo.setEmail(rs.getString("email"));
	        
	                lista.add(vo);

	            }
	        } catch (SQLException e) {
	            System.err.println(e.getMessage());
	            throw new DAOExcepcion(e.getMessage());
	        } finally {
	            this.cerrarResultSet(rs);
	            this.cerrarStatement(stmt);
	            this.cerrarConexion(con);
	        }
	        System.out.println(lista.size());
	        return lista;
	    }

	public Persona obtenerxdocumento(String nroDocumento, String documento) throws DAOExcepcion {
		Persona vo = new Persona();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select nroDocumento, codUsuario, email,nombres,paterno,materno,celular, idpersona from persona where documento=? and nroDocumento=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, documento);
			stmt.setString(2, nroDocumento);
			
			
			
			rs = stmt.executeQuery();
			vo.setIdPersona(-1);
			if (rs.next()) {
				vo.setNroDocumento(rs.getString(1));
				vo.setCodUsuario(rs.getString(2));
				vo.setEmail(rs.getString(3));
				vo.setNombres(rs.getString(4));
				vo.setPaterno(rs.getString(5));
				vo.setMaterno(rs.getString(6));
				vo.setCelular(rs.getString(7));
				vo.setIdPersona(rs.getInt(8));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public Persona obtenerxcodusuario(String codUsuario) throws DAOExcepcion {
		Persona vo = new Persona();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select nroDocumento, codUsuario, email,nombres,paterno,materno,celular, idpersona , contrasena,TipoPersona_idTipoPersona from persona where codUsuario=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, codUsuario);
					
			rs = stmt.executeQuery();
			vo.setIdPersona(-1);
			if (rs.next()) {
				vo.setNroDocumento(rs.getString(1));
				vo.setCodUsuario(rs.getString(2));
				vo.setEmail(rs.getString(3));
				vo.setNombres(rs.getString(4));
				vo.setPaterno(rs.getString(5));
				vo.setMaterno(rs.getString(6));
				vo.setCelular(rs.getString(7));
				vo.setIdPersona(rs.getInt(8));
				vo.setContrasena(rs.getString(9));
				vo.setTipoPersona_idTipoPersona(rs.getInt(10));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}

	public Persona obtenerxemail(String email) throws DAOExcepcion {
		Persona vo = new Persona();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String query = "select nroDocumento, codUsuario, email,nombres,paterno,materno,celular, idpersona from persona where email=?";
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setString(1, email);
					
			rs = stmt.executeQuery();
			vo.setIdPersona(-1);
			if (rs.next()) {
				vo.setNroDocumento(rs.getString(1));
				vo.setCodUsuario(rs.getString(2));
				vo.setEmail(rs.getString(3));
				vo.setNombres(rs.getString(4));
				vo.setPaterno(rs.getString(5));
				vo.setMaterno(rs.getString(6));
				vo.setCelular(rs.getString(7));
				vo.setIdPersona(rs.getInt(8));
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(rs);
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return vo;
	}
	public Collection<Persona> validar_persona(Persona vop)
            throws DAOExcepcion {
        String query = "select nombres from persona Where email=? or codusuario=? or (Documento=? and NroDocumento=?)";
        Collection<Persona> lista = new ArrayList<Persona>();

        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConexionBD.obtenerConexion();
            stmt = con.prepareStatement(query);
            stmt.setString(1,vop.getEmail());
            stmt.setString(2,vop.getCodUsuario());
            stmt.setString(3,vop.getDocumento());
            stmt.setString(4,vop.getNroDocumento());            
            rs = stmt.executeQuery();
            while (rs.next()) {
                Persona vo = new Persona();
                vo.setNombres(rs.getString("nombres"));                
                lista.add(vo);

            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            this.cerrarResultSet(rs);
            this.cerrarStatement(stmt);
            this.cerrarConexion(con);
        }
        System.out.println(lista.size());
        return lista;
    }
	
	
}